using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Text;
using MySql.Data.Common;
using MySql.Data.MySqlClient;
using myconn = MySql.Data.MySqlClient.MySqlConnection;
using SharpSoft.Data.GSQL;

namespace SharpSoft.Data
{
    using System.ComponentModel;
    using System.IO;

    [DisplayName("Mysql数据库")]
    public class MySql : Database
    {
        public MySql(string p_connstr) : base(MySqlClientFactory.Instance, p_connstr)
        {
        }

        /// <summary>
        /// 创建mysql连接字符串
        /// </summary>
        /// <param name="server"></param>
        /// <param name="port"></param>
        /// <param name="defdb"></param>
        /// <param name="uid"></param>
        /// <param name="pwd"></param>
        /// <param name="allowuservariables"></param>
        /// <returns></returns>
        public static string CreateConnectionString(string server, uint port, string defdb, string uid, string pwd, bool allowuservariables = true)
        {
            MySqlConnectionStringBuilder sb = new MySqlConnectionStringBuilder();
            sb.Server = server;
            sb.Port = (port <= 0u ? 3306u : port);
            if (!string.IsNullOrEmpty(defdb))
            {
                sb.Database = defdb;
            }
            sb.UserID = uid;
            sb.Password = pwd;
            sb.AllowUserVariables = allowuservariables;

            return sb.GetConnectionString(true);

            //var str = $"Server={server};Port={(port <= 0 ? 3306 : port)};" +
            //   (string.IsNullOrWhiteSpace(defdb) ? "" : $"Database={defdb};") +
            //    $"Uid={uid};Pwd={pwd};";
            //if (allowuservariables)
            //{
            //    str += "AllowUserVariables=True;";
            //}
            //return str;
        }
        public static string CreateConnectionString(Action<MySqlConnectionStringBuilder> action)
        {
            MySqlConnectionStringBuilder sb = new MySqlConnectionStringBuilder();
            action?.Invoke(sb);
            return sb.GetConnectionString(true);
        }

        /**MYSQL连接字符串参数列表
         * 
         * Server，host, data source, datasource, address, addr, network address: 数据库位置(以上任何关键字均可)
            Database，initial catalog：数据库名
            Port：        socket 端口，默认 3306
            ConnectionProtocol，protocol：    连接协议，默认　Sockets
            PipeName，pipe：        连接管道，默认 MYSQL
            UseCompression，compress：    连接是否压缩，默认 false
            AllowBatch：    是否允许一次执行多条SQL语句，默认 true
            Logging：    是否启用日志，默认 false
            SharedMemoryName：内存共享的名称，默认 MYSQL
            UseOldSyntax，old syntax, oldsyntax：是否兼容旧版的语法，默认 false
            ConnectionTimeout，connection timeout：连接超时等待时间，默认15s
            DefaultCommandTimeout，command timeout：MySqlCommand 超时时间，默认 30s
            UserID, uid, username, user name, user：数据库登录帐号
            Password，pwd：    登录密码
            PersistSecurityInfo：是否保持敏感信息，默认 false
            Encrypt：已经用 SSL 替代了，默认 false
            CertificateFile：证书文件(.pfx)格式
            CertificatePassword：证书的密码
            CertificateStoreLocation：证书的存储位置
            CertificateThumbprint：证书指纹
            AllowZeroDateTime：日期时间能否为零，默认 false
            ConvertZeroDateTime：为零的日期时间是否转化为 DateTime.MinValue，默认 false
            UseUsageAdvisor, usage advisor：是否启用助手，会影响数据库性能，默认 false
            ProcedureCacheSize，procedure cache, procedurecache：同一时间能缓存几条存储过程，0为禁止，默认 25
            UsePerformanceMonitor，userperfmon, perfmon：是否启用性能监视，默认 false
            IgnorePrepare：    是否忽略 Prepare() 调用，默认 true
            UseProcedureBodies,procedure bodies：是否检查存储过程体、参数的有效性，默认 true
            AutoEnlist：    是否自动使用活动的连接，默认 true
            RespectBinaryFlags：是否响应列上元数据的二进制标志，默认 true
            TreatTinyAsBoolean：是否将 TINYINT(1) 列视为布尔型，默认 true
            AllowUserVariables：是否允许 SQL 中出现用户变量，默认 false
            InteractiveSession，interactive：会话是否允许交互，默认 false
            FunctionsReturnString：所有服务器函数是否按返回字符串处理，默认 false
            UseAffectedRows：是否用受影响的行数替代查找到的行数来返回数据，默认 false
            OldGuids：    是否将 binary(16) 列作为 Guids，默认 false
            Keepalive：    保持 TCP 连接的秒数，默认0，不保持。
            ConnectionLifeTime：连接被销毁前在连接池中保持的最少时间（秒）。默认 0
            Pooling：    是否使用线程池，默认 true
            MinimumPoolSize, min pool size：线程池中允许的最少线程数，默认 0
            MaximumPoolSize，max pool size：线程池中允许的最多线程数，默认 100
            ConnectionReset：连接过期后是否自动复位，默认 false
            CharacterSet, charset：向服务器请求连接所使用的字符集，默认：无
            TreatBlobsAsUTF8：binary blobs 是否按 utf8 对待，默认 false
            BlobAsUTF8IncludePattern：列的匹配模式，一旦匹配将按 utf8 处理，默认：无
            SslMode：    是否启用 SSL 连接模式，默认：MySqlSslMode.None
         */

        public override void Dispose()
        {
            if (this.ClearPoolWhenDispose)
            {
                myconn.ClearPool((MySqlConnection)this.conn);
            }
            base.Dispose();
        }

        public override SQLTextGenerator SQLTextGenerator()
        {
            return new MySqlTextGenerator();
        }

        public static Database CreateSchema(string defaultConnStr, string schemaName)
        {
            MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder(defaultConnStr);
            var sql = $"CREATE DATABASE {schemaName};";
            MySqlConnection connection = new MySqlConnection(defaultConnStr);
            connection.Open();
            MySqlCommand command = new MySqlCommand(sql, connection);
            command.ExecuteNonQuery();
            connection.Close();
            builder.Database = schemaName;
            return new MySql(builder.ToString());
        }
        /// <summary>
        /// 使用Mysql原生语法进行分页查询
        /// </summary>
        /// <typeparam name="TModel">单行数据的模型</typeparam>
        /// <param name="select">查询所有数据的select语句</param>
        /// <param name="onepagerowscount">一页数据的数量</param>
        /// <param name="curpageindex">要查询数据的页索引（页索引从0开始）</param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public override SqlPagination<TModel> ExecutePagination<TModel>(NativeSQLCommandScript select, int onepagerowscount, int curpageindex, object paras = null)
        {
            if (onepagerowscount < 1)
            {
                throw new Exception("分页查询时，一页的数量不能小于1.");
            }
            if (curpageindex < 0)
            {
                throw new Exception("分页查询时当前页索引不能小于0.");
            }
            if (!string.IsNullOrWhiteSpace(select.CommandText))
            {
                select.CommandText = select.CommandText.Trim().TrimEnd(';');//使用原生语句时需要去掉子查询中的分号，不然会有语法错误 
            }
            if (string.IsNullOrWhiteSpace(select.CommandText))
            {
                throw new Exception("分页查询指定的查询语句不能为空");
            }
            var sqlcount = (NativeSQLCommandScript)$"SELECT COUNT(1) FROM ({select.CommandText}) AS table1___1;";
            var allcount = this.ExecuteInt32(sqlcount, paras);
            var pagecount = (int)Math.Ceiling((decimal)allcount / (decimal)onepagerowscount);
            int limitstart = curpageindex * onepagerowscount;

            string query = (NativeSQLCommandScript)$"SELECT * FROM ({select.CommandText}) AS table2___2 LIMIT {limitstart},{onepagerowscount}";
            var data = this.ExecuteArray<TModel>(query, paras);
            SqlPagination<TModel> pagi = new SqlPagination<TModel>() { CurrentPage = curpageindex, PageCount = pagecount, AllDataCount = allcount };
            pagi.PageData = data;
            return pagi;
        }
        /// <summary>
        /// 导入数据
        /// </summary>
        /// <param name="filename">文件名</param>
        /// <param name="table">要导入的数据表</param>
        /// <param name="ignoreOrReplace">IGNORE或者REPLACE</param>
        /// <param name="columns">明确指定要导入的列，可为空。列可以使用变量以将数据的该列存入变量，之后可以忽略此列或在SET字句中处理。</param>
        /// <param name="setsub">指定SET字句，可为空</param>
        /// <param name="fieldsTermBy">字段分割符号</param>
        /// <param name="enclosed">字符串的包裹符号</param>
        /// <param name="escaped">字符串内的转义符</param>
        /// <param name="lineTerm">行的分割符</param>
        /// <returns></returns>
        public int ImportData(string filename, string table, string ignoreOrReplace = "IGNORE", string[] columns = null, string setsub = null, string fieldsTermBy = "','", string enclosed = "'\"'", string escaped = "'\\\\'", string lineTerm = "'\\n'")
        {
            string tempfile = System.IO.Path.Combine(GetMysqlSecureFolder(), $"{Guid.NewGuid()}.import");
            System.IO.File.Copy(filename, tempfile, true);
            try
            {
                string colsstr = "";
                if (columns != null && columns.Length != 0)
                {
                    colsstr = "\n(" + string.Join(',', columns) + ")";
                }
                string setsubstr = "";
                if (!string.IsNullOrEmpty(setsub))
                {
                    setsubstr = "\nSET " + setsub;
                }
                string text4 = @$"LOAD DATA INFILE {SqlUtility.ConvertToTSqlText(tempfile)} {ignoreOrReplace} INTO TABLE `{table}`
FIELDS TERMINATED BY {fieldsTermBy}
OPTIONALLY ENCLOSED BY {enclosed}
ESCAPED BY {escaped}
LINES TERMINATED BY {lineTerm} {colsstr} {setsubstr};";
                return base.ExecuteNonQuery(text4, null);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                System.IO.File.Delete(tempfile);
            }
        }
        /// <summary>
        /// 获取Mysql的安全目录
        /// </summary>
        /// <param name="db"></param>
        /// <returns></returns>
        public string? GetMysqlSecureFolder()
        {
            var sql = "SHOW VARIABLES LIKE 'secure_file_priv';";
            var row = this.ExecuteDataRow(sql);
            if (row != null)
            {
                var uploadPath = row["Value"].ToString();
                return uploadPath;
            }
            return null;
        }

        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="sqlquery">查询语句</param>
        /// <param name="paras">参数</param>
        /// <param name="fieldsTerm">字段分割符</param>
        /// <param name="enclosed">将字段括起来的符号，默认为双引号</param>
        /// <param name="linesStarting">行开头字符</param>
        /// <param name="lineTerminated">行分隔符</param>
        /// <returns></returns>
        public string ExportData(string sqlquery, object paras, string fieldsTerm = "','", string enclosed = "'\\\"'", string linesStarting = "''", string lineTerminated = "'\\n'")
        {
            string tempfile = System.IO.Path.Combine(GetMysqlSecureFolder(), $"{Guid.NewGuid()}.export");
            var sql = $@"{sqlquery} INTO OUTFILE {SqlUtility.ConvertToTSqlText(tempfile)}
FIELDS TERMINATED BY {fieldsTerm}
OPTIONALLY ENCLOSED BY {enclosed}
LINES STARTING BY {linesStarting}
TERMINATED BY {lineTerminated}
;";
            this.ExecuteNonQuery(sql, paras);
            return tempfile;
        }

    }
}
